﻿using System;
using System.Data;
using System.Data.SqlClient;
//using Microsoft.ApplicationBlocks.Data;


namespace AX.DB
{
    public class DataIntDB
    {
        //private string _connstring = "data source=(local);initial catalog=Northwind;uid=csharp;pwd=c#.net2004;";
        private SqlConnection _conn;
        ///构造函数
        public DataIntDB(string DBConnectString)  
        {
            OpenConnection(DBConnectString);
        }

        public void OpenConnection(string ConnString)
        {
            if (_conn == null)
            {
                _conn = new SqlConnection();
                _conn.ConnectionString = ConnString;
                _conn.Open();
            }
            else
            {
                if (!this.IsOpen)
                {
                    _conn.Open();
                }
            }
        }

        public bool IsOpen
        {
            get
            {
                return _conn.State == ConnectionState.Open;
            }
        }

        public void CloseDB()
        {

            if (_conn != null)
            {
                if (this.IsOpen)
                {
                    _conn.Close();
                    _conn.Dispose();
                    _conn = null;
                }
            }
        }

        //下面的函数完成单个数据适配器来完成数据集的填充，
        public DataTable Get_HX_Datas_New(string StartDate, string EndDate, string DwCode,int TP)
        {
            if (!string.IsNullOrEmpty(DwCode))
            {
                return Get_DW_HXDatas(StartDate, EndDate, DwCode);
            }
            string strSql;
            string str_Param;
            str_Param = "";
            string strTmp = "Get_hxDetail_all";

            strSql = "DECLARE @return_value int EXEC @return_value = Get_hxDetail_all {0} SELECT 'Return Value' = @return_value";

            if (!string.IsNullOrEmpty(StartDate))
            {
                str_Param += "@dStartDate = N'" + StartDate + "'";
            }
            if (!string.IsNullOrEmpty(EndDate))
            {
                if (str_Param.Trim() != "") str_Param += ",";
                str_Param += "@dEndDate = N'" + EndDate + "'";
            }
            if (str_Param.Trim() != "") str_Param += ",";
            if (TP == 0)
            {
                str_Param += "@Account = N'002'";
            }
            else
            {
                str_Param += "@Account = N'006'";
            }
            if (str_Param.Trim() != "") str_Param += ",";
            string strYear;
            try
            {
                strYear=DateTime.Parse(StartDate).Year.ToString().Trim();
            }
            catch
            {
                strYear=DateTime.Now.Year.ToString().Trim();
            }
            str_Param += "@Year = N'" + strYear + "'";

            strSql = string.Format(strSql, str_Param);
            DataSet ds = new DataSet();
            SqlCommand comm = new SqlCommand(strSql, _conn);
            comm.CommandType = CommandType.Text;
            //comm.CommandText = strSql;
            SqlDataAdapter objDapter = new SqlDataAdapter(comm);
            //objDapter.SelectCommand = comm;
            int intcols = objDapter.Fill(ds, "aa");
            if (ds != null)
            {
                if (ds.Tables[0].Columns.Count <= 1)
                    return null;
                else
                    return ds.Tables[0];
            }
            return null;
        }

        public DataTable Get_HX_Datas(string StartDate, string EndDate, string DwCode, int TP)
        {
            if (!string.IsNullOrEmpty(DwCode))
            {
                return Get_DW_HXDatas(StartDate, EndDate, DwCode);
            }
            string strSql;
            string str_Param;
            str_Param = "";
            string strTmp;
            string strYear;
            try
            {
                strYear = DateTime.Parse(StartDate).Year.ToString().Trim();
            }
            catch
            {
                strYear = DateTime.Now.Year.ToString().Trim();
            }
            if (TP == 0)
            {
                strTmp = "Get_002_" + strYear.Trim() + "_hxDetail_all";
            }
            else
            {
                strTmp = "Get_006_" + strYear.Trim() + "_hxDetail_all";
            }


            strSql = "DECLARE @return_value int EXEC @return_value = "+strTmp+" {0} SELECT 'Return Value' = @return_value";

            if (!string.IsNullOrEmpty(StartDate))
            {
                str_Param += "@dStartDate = N'" + StartDate + "'";
            }
            if (!string.IsNullOrEmpty(EndDate))
            {
                if (str_Param.Trim() != "") str_Param += ",";
                str_Param += "@dEndDate = N'" + EndDate + "'";
            }
          

            strSql = string.Format(strSql, str_Param);
            DataSet ds = new DataSet();
            SqlCommand comm = new SqlCommand(strSql, _conn);
            comm.CommandType = CommandType.Text;
            //comm.CommandText = strSql;
            SqlDataAdapter objDapter = new SqlDataAdapter(comm);
            //objDapter.SelectCommand = comm;
            int intcols = objDapter.Fill(ds, "aa");
            if (ds != null)
            {
                if (ds.Tables[0].Columns.Count <= 1)
                    return null;
                else
                    return ds.Tables[0];
            }
            return null;
        }




        private DataTable Get_DW_HXDatas(string StartDate, string EndDate, string DwCode)
        {
            string strSql;
            strSql = "DECLARE @return_value int EXEC @return_value = dbo.GethxDetail_kh {0} SELECT 'Return Value' = @return_value";
            string str_Param;
            str_Param = "";

            if (!string.IsNullOrEmpty(DwCode))
            {
                str_Param += "@cDwCode = N'" + DwCode + "'";
            }
            if (!string.IsNullOrEmpty(StartDate))
            {
                if (str_Param.Trim() != "") str_Param += ",";
                str_Param += "@dStartDate = N'" + StartDate + "'";
            }

            if (!string.IsNullOrEmpty(EndDate))
            {
                if (str_Param.Trim() != "") str_Param += ",";
                str_Param += "@dEndDate = N'" + EndDate + "'";
            }
            strSql = string.Format(strSql, str_Param);
            DataSet ds = new DataSet();
            SqlCommand comm = new SqlCommand(strSql, _conn);
            comm.CommandType = CommandType.Text;
            //comm.CommandText = strSql;
            SqlDataAdapter objDapter = new SqlDataAdapter(comm);
            //objDapter.SelectCommand = comm;
            int intcols = objDapter.Fill(ds, "aa");
            if (ds != null)
            {
                if (ds.Tables[0].Columns.Count <= 1)
                    return null;
                else
                    return ds.Tables[0];
            }
            return null;
        }
    }
}
